"""
业务工具函数
提供订单查询、商品搜索、物流追踪等功能
"""
from typing import Optional, List, Dict, Any
from datetime import datetime
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from loguru import logger

from app.models.order import Order
from app.db.redis_client import redis_manager
from app.models.product import Product, UserPreference, ProductPriceHistory


# ==================== 订单查询工具 ====================

async def search_order(
    order_sn: str,
    db: AsyncSession
) -> Optional[Dict[str, Any]]:
    """
    查询订单信息
    
    Args:
        order_sn: 订单号
        db: 数据库会话
        
    Returns:
        订单信息字典或None
    """
    try:
        logger.info(f"查询订单: {order_sn}")
        
        # 先从缓存查询
        cache_key = f"order:{order_sn}"
        cached_order = await redis_manager.cache_get(cache_key)
        if cached_order:
            logger.info(f"从缓存获取订单: {order_sn}")
            return cached_order
        
        # 从数据库查询
        stmt = select(Order).where(Order.order_sn == order_sn)
        result = await db.execute(stmt)
        order = result.scalar_one_or_none()
        
        if not order:
            logger.warning(f"订单不存在: {order_sn}")
            return None
        
        # 格式化订单信息
        order_info = {
            "order_sn": order.order_sn,
            "status": order.status.value if hasattr(order.status, 'value') else order.status,
            "total_amount": float(order.total_amount),
            "created_at": order.created_at.isoformat() if order.created_at else None,
            "product_name": order.product_name,
            "quantity": order.quantity,
            "shipping_address": order.full_address,
            "receiver_name": order.receiver_name,
            "receiver_phone": order.receiver_phone,
            "tracking_number": order.shipping_no,
            "shipping_company": order.shipping_company,
            "payment_method": order.payment_method.value if order.payment_method and hasattr(order.payment_method, 'value') else order.payment_method,
            "paid_amount": float(order.paid_amount) if order.paid_amount else None,
            "buyer_message": order.buyer_message,
        }
        
        # 缓存5分钟
        await redis_manager.cache_set(cache_key, order_info, expire=300)
        
        logger.info(f"订单查询成功: {order_sn}")
        return order_info
        
    except Exception as e:
        logger.error(f"查询订单失败: {e}")
        return None


async def get_user_orders(
    user_id: int,
    db: AsyncSession,
    status: Optional[str] = None,
    limit: int = 10
) -> List[Dict[str, Any]]:
    """
    获取用户的订单列表
    
    Args:
        user_id: 用户ID
        db: 数据库会话
        status: 订单状态过滤
        limit: 返回数量限制
        
    Returns:
        订单列表
    """
    try:
        logger.info(f"获取用户订单列表: user_id={user_id}, status={status}")
        
        # 构建查询
        stmt = select(Order).where(Order.user_id == user_id)
        
        if status:
            stmt = stmt.where(Order.status == status)
        
        stmt = stmt.order_by(Order.created_at.desc()).limit(limit)
        
        result = await db.execute(stmt)
        orders = result.scalars().all()
        
        # 格式化订单列表
        order_list = []
        for order in orders:
            order_list.append({
                "order_sn": order.order_sn,
                "status": order.status.value if hasattr(order.status, 'value') else order.status,
                "total_amount": float(order.total_amount),
                "created_at": order.created_at.isoformat() if order.created_at else None,
                "product_name": order.product_name,
                "quantity": order.quantity,
            })
        
        logger.info(f"找到 {len(order_list)} 个订单")
        return order_list
        
    except Exception as e:
        logger.error(f"获取用户订单列表失败: {e}")
        return []


# ==================== 物流追踪工具 ====================

async def track_logistics(
    order_sn: Optional[str] = None,
    tracking_number: Optional[str] = None,
    db: Optional[AsyncSession] = None
) -> Optional[Dict[str, Any]]:
    """
    追踪物流信息
    
    Args:
        order_sn: 订单号
        tracking_number: 快递单号
        db: 数据库会话
        
    Returns:
        物流信息字典或None
    """
    try:
        logger.info(f"追踪物流: order_sn={order_sn}, tracking_number={tracking_number}")
        
        # 如果提供了订单号，先查订单获取快递单号
        if order_sn and db:
            order = await search_order(order_sn, db)
            if order and order.get("tracking_number"):
                tracking_number = order["tracking_number"]
        
        if not tracking_number:
            logger.warning("未提供有效的快递单号")
            return None
        
        # 先从缓存查询
        cache_key = f"logistics:{tracking_number}"
        cached_logistics = await redis_manager.cache_get(cache_key)
        if cached_logistics:
            logger.info(f"从缓存获取物流信息: {tracking_number}")
            return cached_logistics
        
        # TODO: 集成真实的物流API
        # 这里使用模拟数据
        logistics_info = _mock_logistics_data(tracking_number)
        
        # 缓存10分钟
        await redis_manager.cache_set(cache_key, logistics_info, expire=600)
        
        logger.info(f"物流查询成功: {tracking_number}")
        return logistics_info
        
    except Exception as e:
        logger.error(f"追踪物流失败: {e}")
        return None


def _mock_logistics_data(tracking_number: str) -> Dict[str, Any]:
    """模拟物流数据（用于演示）"""
    return {
        "tracking_number": tracking_number,
        "courier": "顺丰速运",
        "status": "运输中",
        "current_location": "上海市分拨中心",
        "estimated_delivery": "2025-10-28",
        "traces": [
            {
                "time": "2025-10-26 10:30",
                "location": "上海市分拨中心",
                "status": "到达中转站"
            },
            {
                "time": "2025-10-26 08:00",
                "location": "杭州市",
                "status": "已发出"
            },
            {
                "time": "2025-10-25 18:00",
                "location": "杭州市",
                "status": "已揽收"
            }
        ]
    }


# ==================== 商品搜索工具 ====================

async def search_product(
    keyword: str,
    category: Optional[str] = None,
    limit: int = 10
) -> List[Dict[str, Any]]:
    """
    搜索商品
    
    Args:
        keyword: 搜索关键词
        category: 商品分类
        limit: 返回数量限制
        
    Returns:
        商品列表
    """
    try:
        logger.info(f"搜索商品: keyword={keyword}, category={category}")
        
        # 先从缓存查询（v2版本，修正了字段名）
        cache_key = f"product_search_v2:{keyword}:{category}:{limit}"
        cached_products = await redis_manager.cache_get(cache_key)
        if cached_products:
            logger.info("从缓存获取商品搜索结果")
            return cached_products
        
        # TODO: 集成真实的商品搜索
        # 这里使用模拟数据
        products = _mock_product_data(keyword, category, limit)
        
        # 缓存30分钟
        await redis_manager.cache_set(cache_key, products, expire=1800)
        
        logger.info(f"找到 {len(products)} 个商品")
        return products
        
    except Exception as e:
        logger.error(f"搜索商品失败: {e}")
        return []


def _mock_product_data(keyword: str, category: Optional[str], limit: int) -> List[Dict[str, Any]]:
    """模拟商品数据（用于演示）"""
    # 根据关键词生成模拟商品
    products = []
    
    if "手机" in keyword or "iphone" in keyword.lower():
        products.extend([
            {
                "product_sn": "PHONE-IP15P-001",
                "name": "iPhone 15 Pro Max 256GB",
                "price": 9999.00,
                "stock": 100,
                "category": "数码产品",
                "description": "A17 Pro芯片，钛金属设计",
                "image": "https://example.com/iphone15.jpg"
            },
            {
                "product_sn": "PHONE-IP15-001",
                "name": "iPhone 15 128GB",
                "price": 5999.00,
                "stock": 200,
                "category": "数码产品",
                "description": "A16仿生芯片，灵动岛设计",
                "image": "https://example.com/iphone15.jpg"
            }
        ])
    
    if "笔记本" in keyword or "电脑" in keyword:
        products.extend([
            {
                "product_id": "P003",
                "name": "MacBook Pro 14英寸 M3",
                "price": 14999.00,
                "stock": 50,
                "category": "数码产品",
                "description": "M3芯片，16GB内存，512GB存储",
                "image": "https://example.com/macbook.jpg"
            }
        ])
    
    if "耳机" in keyword:
        products.extend([
            {
                "product_id": "P004",
                "name": "AirPods Pro 2",
                "price": 1899.00,
                "stock": 300,
                "category": "数码配件",
                "description": "主动降噪，空间音频",
                "image": "https://example.com/airpods.jpg"
            }
        ])
    
    # 如果没有匹配，返回通用商品
    if not products:
        products = [
            {
                "product_id": "P999",
                "name": f"搜索结果：{keyword}",
                "price": 999.00,
                "stock": 10,
                "category": category or "其他",
                "description": "暂无详细信息",
                "image": "https://example.com/default.jpg"
            }
        ]
    
    return products[:limit]


# ==================== 售后服务工具 ====================

async def create_refund_request(
    user_id: int,
    order_sn: str,
    reason: str,
    db: AsyncSession
) -> Dict[str, Any]:
    """
    创建退款申请
    
    Args:
        user_id: 用户ID
        order_sn: 订单号
        reason: 退款原因
        db: 数据库会话
        
    Returns:
        退款申请结果
    """
    try:
        logger.info(f"创建退款申请: user_id={user_id}, order_sn={order_sn}")
        
        # 查询订单
        order = await search_order(order_sn, db)
        if not order:
            return {
                "success": False,
                "message": "订单不存在"
            }
        
        # 检查订单状态
        if order["status"] not in ["已完成", "已发货"]:
            return {
                "success": False,
                "message": f"订单状态为'{order['status']}'，不支持退款"
            }
        
        # TODO: 创建退款记录
        refund_id = f"RF{datetime.now().strftime('%Y%m%d%H%M%S')}"
        
        return {
            "success": True,
            "message": "退款申请已提交",
            "refund_id": refund_id,
            "estimated_time": "3-5个工作日"
        }
        
    except Exception as e:
        logger.error(f"创建退款申请失败: {e}")
        return {
            "success": False,
            "message": f"系统错误: {str(e)}"
        }


# ==================== 优惠查询工具 ====================

async def get_available_coupons(user_id: int) -> List[Dict[str, Any]]:
    """
    获取用户可用优惠券
    
    Args:
        user_id: 用户ID
        
    Returns:
        优惠券列表
    """
    try:
        logger.info(f"获取用户优惠券: user_id={user_id}")
        
        # 先从缓存查询
        cache_key = f"coupons:{user_id}"
        cached_coupons = await redis_manager.cache_get(cache_key)
        if cached_coupons:
            return cached_coupons
        
        # TODO: 从数据库查询
        # 使用模拟数据
        coupons = [
            {
                "coupon_id": "C001",
                "name": "新用户专享券",
                "discount": 50.00,
                "min_amount": 200.00,
                "expire_date": "2025-11-30"
            },
            {
                "coupon_id": "C002",
                "name": "全品类满减券",
                "discount": 30.00,
                "min_amount": 300.00,
                "expire_date": "2025-12-31"
            }
        ]
        
        # 缓存1小时
        await redis_manager.cache_set(cache_key, coupons, expire=3600)
        
        return coupons
        
    except Exception as e:
        logger.error(f"获取优惠券失败: {e}")
        return []


# ==================== 商品导购工具 ====================

async def recommend_products(
    requirements: str,
    min_price: Optional[float] = None,
    max_price: Optional[float] = None,
    category: Optional[str] = None,
    user_id: Optional[int] = None,
    db: Optional[AsyncSession] = None,
    limit: int = 5
) -> Dict[str, Any]:
    """
    智能推荐商品
    
    Args:
        requirements: 用户需求描述，例如："主要用来拍照"
        min_price: 最低价格
        max_price: 最高价格
        category: 商品分类
        user_id: 用户ID（用于个性化推荐）
        db: 数据库会话
        limit: 返回数量
        
    Returns:
        推荐结果字典
    """
    try:
        logger.info(f"智能推荐 | 需求:{requirements} | 价格:{min_price}-{max_price} | 分类:{category}")
        
        if not db:
            logger.error("数据库会话未提供")
            return {
                "success": False,
                "message": "系统错误：数据库连接失败",
                "products": []
            }
        
        # 🔧 智能关键词识别和分类映射
        requirements_lower = requirements.lower()
        detected_category = None
        detected_sub_category = None
        
        # 关键词到分类的映射
        keyword_mapping = {
            "手机": ("数码产品", "手机"),
            "电脑": ("数码产品", "笔记本电脑"),
            "笔记本": ("数码产品", "笔记本电脑"),
            "笔记本电脑": ("数码产品", "笔记本电脑"),
            "耳机": ("数码配件", "耳机"),
            "手表": ("数码产品", "智能手表"),
            "智能手表": ("数码产品", "智能手表"),
            "鞋": ("服饰鞋包", "运动鞋"),
            "运动鞋": ("服饰鞋包", "运动鞋"),
            "羽绒服": ("服饰鞋包", "羽绒服"),
            "空气净化器": ("家居生活", "空气净化器"),
            "电水壶": ("家居生活", "小家电"),
            "面膜": ("美妆护肤", "面膜"),
            "坚果": ("食品生鲜", "零食坚果"),
            "纸尿裤": ("母婴用品", "纸尿裤"),
            "书": ("图书音像", "文学小说"),
            "图书": ("图书音像", "文学小说"),
        }
        
        # 从需求描述中识别商品类别
        for keyword, (cat, sub_cat) in keyword_mapping.items():
            if keyword in requirements_lower:
                detected_category = cat
                detected_sub_category = sub_cat
                logger.info(f"🎯 识别关键词'{keyword}' → 分类:{cat} > {sub_cat}")
                break
        
        # 构建查询
        stmt = select(Product).where(Product.is_active == True)
        
        # 价格筛选
        if min_price is not None:
            stmt = stmt.where(Product.price >= min_price)
        if max_price is not None:
            stmt = stmt.where(Product.price <= max_price)
        
        # 分类筛选 - 优先使用识别的分类，其次使用传入的参数
        filter_category = detected_category or category
        filter_sub_category = detected_sub_category
        
        if filter_category:
            stmt = stmt.where(Product.category == filter_category)
            logger.info(f"📂 按分类筛选: {filter_category}")
        
        if filter_sub_category:
            stmt = stmt.where(Product.sub_category == filter_sub_category)
            logger.info(f"📂 按子分类筛选: {filter_sub_category}")
        
        # 只推荐库存充足的商品
        stmt = stmt.where(Product.stock > 0)
        
        # 按照推荐分数排序（热度 * 0.3 + 品质 * 0.4 + 性价比 * 0.3）
        stmt = stmt.order_by(
            (Product.hot_score * 0.3 + Product.quality_score * 0.4 + Product.cost_performance * 0.3).desc()
        ).limit(limit * 2)  # 多取一些，后续筛选
        
        result = await db.execute(stmt)
        products = result.scalars().all()
        
        if not products:
            return {
                "success": True,
                "message": "暂未找到符合条件的商品",
                "products": []
            }
        
        # 获取用户偏好（如果提供了user_id）
        user_preference = None
        if user_id:
            pref_result = await db.execute(
                select(UserPreference).where(UserPreference.user_id == user_id)
            )
            user_preference = pref_result.scalar_one_or_none()
        
        # 生成推荐结果
        recommendations = []
        for product in products[:limit]:
            # 生成推荐理由
            reasons = _generate_recommendation_reasons(
                product, requirements, min_price, max_price, user_preference
            )
            
            # 计算匹配度分数
            match_score = _calculate_match_score(
                product, requirements, min_price, max_price, user_preference
            )
            
            recommendations.append({
                "product_id": product.id,
                "product_sn": product.product_sn,
                "name": product.name,
                "brand": product.brand,
                "category": product.category,
                "price": float(product.price),
                "original_price": float(product.original_price) if product.original_price else None,
                "rating": float(product.rating),
                "review_count": product.review_count,
                "sales_count": product.sales_count,
                "stock": product.stock,
                "main_image": product.main_image,
                "tags": product.tags,
                "features": product.features[:3] if product.features else [],  # 前3个特点
                "suitable_for": product.suitable_for,
                "match_score": match_score,
                "recommendation_reasons": reasons,
                "discount_info": f"省{int(product.original_price - product.price)}元" if product.original_price and product.original_price > product.price else None
            })
        
        # 按匹配度排序
        recommendations.sort(key=lambda x: x["match_score"], reverse=True)
        
        logger.info(f"推荐 {len(recommendations)} 个商品")
        return {
            "success": True,
            "products": recommendations,
            "total": len(recommendations),
            "message": f"为您找到{len(recommendations)}个推荐商品"
        }
        
    except Exception as e:
        logger.error(f"智能推荐失败: {e}", exc_info=True)
        return {
            "success": False,
            "message": f"推荐失败: {str(e)}",
            "products": []
        }


def _generate_recommendation_reasons(
    product: Product,
    requirements: str,
    min_price: Optional[float],
    max_price: Optional[float],
    user_preference: Optional[UserPreference]
) -> List[str]:
    """生成推荐理由"""
    reasons = []
    
    # 价格理由
    if max_price and product.price <= max_price * 0.8:
        reasons.append(f"价格{int(product.price)}元，在预算内性价比高")
    elif max_price and product.price <= max_price:
        reasons.append(f"价格{int(product.price)}元，符合您的预算")
    
    # 品质理由
    if product.quality_score >= 95:
        reasons.append("品质卓越，用户好评如潮")
    elif product.quality_score >= 90:
        reasons.append("品质优秀，质量可靠")
    
    # 热度理由
    if product.hot_score >= 90:
        reasons.append(f"热门商品，已售{product.sales_count}件")
    
    # 评价理由
    if product.rating >= 4.8 and product.review_count > 1000:
        reasons.append(f"{product.rating}星高评分，{product.review_count}+用户好评")
    
    # 用户偏好理由
    if user_preference:
        if product.brand in (user_preference.preferred_brands or []):
            reasons.append(f"您喜欢的{product.brand}品牌")
        if product.category in (user_preference.preferred_categories or []):
            reasons.append("符合您的购物偏好")
    
    # 需求匹配理由（关键词匹配）
    requirements_lower = requirements.lower()
    
    # 电脑相关
    if any(keyword in requirements_lower for keyword in ["电脑", "笔记本", "办公", "工作"]):
        if "笔记本电脑" in (product.sub_category or ""):
            reasons.append("适合办公和日常使用")
        if any(keyword in requirements_lower for keyword in ["视频", "剪辑", "设计"]):
            if "专业" in (product.description or ""):
                reasons.append("专业性能，适合创作")
    
    # 拍照相关
    if any(keyword in requirements_lower for keyword in ["拍照", "摄影", "相机"]):
        if product.tags and any(tag in ["拍照强", "摄影"] for tag in product.tags):
            reasons.append("拍照性能出色")
    
    # 性价比相关
    if any(keyword in requirements_lower for keyword in ["性价比", "便宜", "实惠"]):
        if product.cost_performance >= 90:
            reasons.append("性价比超高")
    
    # 品质相关
    if any(keyword in requirements_lower for keyword in ["品质", "高端", "品牌"]):
        if product.quality_score >= 95:
            reasons.append("高端品质，值得信赖")
    
    # 如果理由太少，补充通用理由
    if len(reasons) < 2:
        if product.is_recommended:
            reasons.append("平台推荐商品")
        if product.features:
            reasons.append(product.features[0])
    
    return reasons[:3]  # 最多返回3个理由


def _calculate_match_score(
    product: Product,
    requirements: str,
    min_price: Optional[float],
    max_price: Optional[float],
    user_preference: Optional[UserPreference]
) -> float:
    """计算商品与需求的匹配度分数（0-100）"""
    score = 0.0
    
    # 基础分数：综合评分 (40%)
    base_score = (product.hot_score * 0.3 + product.quality_score * 0.4 + product.cost_performance * 0.3)
    score += base_score * 0.4
    
    # 价格匹配度 (20%)
    if max_price:
        if product.price <= max_price * 0.8:
            score += 20  # 远低于预算，满分
        elif product.price <= max_price:
            score += 15  # 在预算内
        else:
            score += 5  # 超预算但不多
    else:
        score += 10  # 无预算限制，给一半分
    
    # 用户偏好匹配 (20%)
    if user_preference:
        if product.brand in (user_preference.preferred_brands or []):
            score += 10
        if product.category in (user_preference.preferred_categories or []):
            score += 10
    else:
        score += 10  # 无用户数据，给一半分
    
    # 需求关键词匹配 (20%)
    requirements_lower = requirements.lower()
    match_count = 0
    
    # 🎯 核心关键词匹配（权重更高）
    core_keyword_matched = False
    keyword_to_subcat = {
        "电脑": "笔记本电脑",
        "笔记本": "笔记本电脑",
        "手机": "手机",
        "耳机": "耳机",
        "手表": "智能手表",
    }
    
    for keyword, expected_subcat in keyword_to_subcat.items():
        if keyword in requirements_lower and expected_subcat in (product.sub_category or ""):
            match_count += 3  # 核心关键词匹配，加3分
            core_keyword_matched = True
            break
    
    # 检查tags
    if product.tags:
        for tag in product.tags:
            if tag.lower() in requirements_lower or any(word in requirements_lower for word in tag.lower().split()):
                match_count += 1
    
    # 检查适用场景
    if product.suitable_for:
        for suitable in product.suitable_for:
            if suitable in requirements:
                match_count += 1
    
    score += min(match_count * 5, 20)  # 最多20分
    
    # 🔥 核心关键词完美匹配额外奖励
    if core_keyword_matched:
        score += 10  # 额外10分奖励
    
    return min(score, 100.0)


async def compare_products(
    product_ids: List[int],
    db: AsyncSession
) -> Dict[str, Any]:
    """
    对比多个商品
    
    Args:
        product_ids: 商品ID列表（2-3个）
        db: 数据库会话
        
    Returns:
        对比结果
    """
    try:
        if len(product_ids) < 2:
            return {
                "success": False,
                "message": "至少需要2个商品进行对比"
            }
        
        if len(product_ids) > 3:
            return {
                "success": False,
                "message": "最多支持对比3个商品"
            }
        
        logger.info(f"对比商品: {product_ids}")
        
        # 查询商品
        stmt = select(Product).where(Product.id.in_(product_ids))
        result = await db.execute(stmt)
        products = result.scalars().all()
        
        if len(products) != len(product_ids):
            return {
                "success": False,
                "message": "部分商品不存在"
            }
        
        # 生成对比数据
        comparison = {
            "success": True,
            "products": [],
            "comparison_table": {},
            "winner": {}  # 各项最优者
        }
        
        # 提取对比维度
        dimensions = ["price", "rating", "review_count", "sales_count", "hot_score", "quality_score", "cost_performance"]
        
        for product in products:
            comparison["products"].append({
                "id": product.id,
                "name": product.name,
                "brand": product.brand,
                "price": float(product.price),
                "rating": float(product.rating),
                "review_count": product.review_count,
                "sales_count": product.sales_count,
                "hot_score": float(product.hot_score),
                "quality_score": float(product.quality_score),
                "cost_performance": float(product.cost_performance),
                "features": product.features,
                "specifications": product.specifications,
                "main_image": product.main_image
            })
        
        # 找出各维度最优者
        comparison["winner"] = {
            "lowest_price": min(products, key=lambda p: p.price).name,
            "highest_rating": max(products, key=lambda p: p.rating).name,
            "most_popular": max(products, key=lambda p: p.sales_count).name,
            "best_quality": max(products, key=lambda p: p.quality_score).name,
            "best_value": max(products, key=lambda p: p.cost_performance).name
        }
        
        logger.info(f"对比完成：{len(products)}个商品")
        return comparison
        
    except Exception as e:
        logger.error(f"商品对比失败: {e}", exc_info=True)
        return {
            "success": False,
            "message": f"对比失败: {str(e)}"
        }


async def get_product_details(
    product_id: Optional[int] = None,
    product_sn: Optional[str] = None,
    db: Optional[AsyncSession] = None
) -> Optional[Dict[str, Any]]:
    """
    获取商品详情
    
    Args:
        product_id: 商品ID
        product_sn: 商品编号
        db: 数据库会话
        
    Returns:
        商品详情字典
    """
    try:
        if not db:
            logger.error("数据库会话未提供")
            return None
        
        if not product_id and not product_sn:
            logger.warning("未提供商品ID或编号")
            return None
        
        # 构建查询
        if product_id:
            stmt = select(Product).where(Product.id == product_id)
        else:
            stmt = select(Product).where(Product.product_sn == product_sn)
        
        result = await db.execute(stmt)
        product = result.scalar_one_or_none()
        
        if not product:
            logger.warning(f"商品不存在: id={product_id}, sn={product_sn}")
            return None
        
        # 组装详细信息
        details = {
            "id": product.id,
            "product_sn": product.product_sn,
            "name": product.name,
            "brand": product.brand,
            "category": product.category,
            "sub_category": product.sub_category,
            "price": float(product.price),
            "original_price": float(product.original_price) if product.original_price else None,
            "stock": product.stock,
            "sales_count": product.sales_count,
            "description": product.description,
            "specifications": product.specifications,
            "features": product.features,
            "main_image": product.main_image,
            "images": product.images,
            "rating": float(product.rating),
            "review_count": product.review_count,
            "suitable_for": product.suitable_for,
            "tags": product.tags,
            "hot_score": float(product.hot_score),
            "quality_score": float(product.quality_score),
            "cost_performance": float(product.cost_performance),
            "is_recommended": product.is_recommended,
            "discount_info": f"省{int(product.original_price - product.price)}元" if product.original_price and product.original_price > product.price else None
        }
        
        logger.info(f"获取商品详情: {product.name}")
        return details
        
    except Exception as e:
        logger.error(f"获取商品详情失败: {e}", exc_info=True)
        return None


async def search_product_price_history(
    product_name: str,
    days: int = 5,
    db: Optional[AsyncSession] = None
) -> Optional[Dict[str, Any]]:
    """
    搜索商品并查询价格历史（复合操作）
    
    Args:
        product_name: 商品名称
        days: 查询天数（默认30天）
        db: 数据库会话
        
    Returns:
        价格历史数据和统计信息
    """
    try:
        logger.info(f"搜索商品价格历史: {product_name}, {days}天")
        
        if not db:
            logger.error("数据库会话未提供")
            return None
        
        # 直接从数据库搜索商品（而不是使用模拟数据）
        stmt = select(Product).where(
            Product.name.ilike(f"%{product_name}%")
        ).limit(5)
        result = await db.execute(stmt)
        products = result.scalars().all()
        
        if not products:
            logger.warning(f"未找到商品: {product_name}")
            return {
                "success": False,
                "message": f"未找到商品 {product_name}"
            }
        
        # 取第一个匹配的商品
        product = products[0]
        logger.info(f"找到商品: {product.name} ({product.product_sn})")
        
        # 查询价格历史
        price_history = await get_price_history(
            product_sn=product.product_sn,
            days=days,
            db=db
        )
        
        if not price_history:
            return {
                "success": False,
                "message": f"未找到商品 {product.name} 的价格历史数据"
            }
        
        return {
            "success": True,
            **price_history
        }
        
    except Exception as e:
        logger.error(f"搜索商品价格历史失败: {e}", exc_info=True)
        return None


async def get_price_history(
    product_id: Optional[int] = None,
    product_sn: Optional[str] = None,
    days: int = 30,
    db: Optional[AsyncSession] = None
) -> Optional[Dict[str, Any]]:
    """
    获取商品价格历史
    
    Args:
        product_id: 商品ID
        product_sn: 商品编号
        days: 查询天数（7/30/90）
        db: 数据库会话
        
    Returns:
        价格历史数据和统计信息
    """
    try:
        from datetime import timedelta
        from sqlalchemy import and_, desc, func
        
        if not db:
            logger.error("数据库会话未提供")
            return None
        
        if not product_id and not product_sn:
            logger.warning("未提供商品ID或编号")
            return None
        
        # 先获取商品信息
        if product_id:
            stmt = select(Product).where(Product.id == product_id)
        else:
            stmt = select(Product).where(Product.product_sn == product_sn)
        
        result = await db.execute(stmt)
        product = result.scalar_one_or_none()
        
        if not product:
            logger.warning(f"商品不存在: id={product_id}, sn={product_sn}")
            return None
        
        # 计算时间范围
        end_date = datetime.utcnow()
        start_date = end_date - timedelta(days=days)
        
        # 查询价格历史
        stmt = (
            select(ProductPriceHistory)
            .where(
                and_(
                    ProductPriceHistory.product_id == product.id,
                    ProductPriceHistory.recorded_at >= start_date
                )
            )
            .order_by(ProductPriceHistory.recorded_at)
        )
        
        result = await db.execute(stmt)
        history_records = result.scalars().all()
        
        if not history_records:
            logger.warning(f"没有找到价格历史: {product.name}")
            return {
                "product_id": product.id,
                "product_sn": product.product_sn,
                "product_name": product.name,
                "current_price": float(product.price),
                "message": "暂无历史价格数据"
            }
        
        # 计算统计信息
        prices = [float(r.price) for r in history_records]
        current_price = float(product.price)
        highest_price = max(prices)
        lowest_price = min(prices)
        average_price = sum(prices) / len(prices)
        
        # 计算价格趋势
        if len(prices) >= 2:
            recent_avg = sum(prices[-7:]) / min(7, len(prices[-7:]))
            older_avg = sum(prices[:7]) / min(7, len(prices[:7]))
            if recent_avg > older_avg * 1.05:
                trend = "up"
            elif recent_avg < older_avg * 0.95:
                trend = "down"
            else:
                trend = "stable"
        else:
            trend = "stable"
        
        # 计算变化率
        if prices[0] > 0:
            change_rate = ((current_price - prices[0]) / prices[0]) * 100
        else:
            change_rate = 0
        
        # 是否近期最低价
        is_lowest_recently = current_price <= lowest_price * 1.02
        
        # 组装历史数据
        history_data = [
            {
                "date": r.recorded_at.strftime("%Y-%m-%d"),
                "price": float(r.price),
                "original_price": float(r.original_price) if r.original_price else None,
                "reason": r.change_reason
            }
            for r in history_records
        ]
        
        result_data = {
            "product_id": product.id,
            "product_sn": product.product_sn,
            "product_name": product.name,
            "days": days,
            "current_price": current_price,
            "statistics": {
                "highest_price": round(highest_price, 2),
                "lowest_price": round(lowest_price, 2),
                "average_price": round(average_price, 2),
                "price_trend": trend,
                "change_rate": round(change_rate, 2),
                "is_lowest_recently": is_lowest_recently
            },
            "history": history_data,
            "total": len(history_data)
        }
        
        logger.info(f"获取价格历史: {product.name}, {days}天, {len(history_data)}条记录")
        return result_data
        
    except Exception as e:
        logger.error(f"获取价格历史失败: {e}", exc_info=True)
        return None


# ==================== 工具函数注册表 ====================

# 用于AI Agent调用的工具函数定义（OpenAI Function Calling格式）
TOOLS_DEFINITION = [
    {
        "type": "function",
        "function": {
            "name": "search_order",
            "description": """【高优先级工具】查询订单信息。
            
使用场景：
- 用户消息中包含订单号（格式：ORD + 11位或以上数字，如 ORD20251108001、ORD20251108002）
- 用户询问订单状态、物流、发货情况等
- 无论用户如何表达（"查一下"、"这个呢"、"帮我看看"等），只要有订单号就必须调用此工具

重要提示：
1. 检测到订单号格式（ORD开头+数字）时，立即调用此工具，不要犹豫
2. 即使用户表达模糊（如"这个呢ORD20251108002"），也要调用
3. 不要假设订单不存在，必须调用工具确认

返回信息：订单详情、状态、物流信息、收货地址等完整订单信息""",
            "parameters": {
                "type": "object",
                "properties": {
                    "order_sn": {
                        "type": "string",
                        "description": "订单号，例如：ORD20251026001、ORD20251108001"
                    }
                },
                "required": ["order_sn"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "track_logistics",
            "description": "追踪物流信息，根据订单号或快递单号查询物流状态和配送进度",
            "parameters": {
                "type": "object",
                "properties": {
                    "order_sn": {
                        "type": "string",
                        "description": "订单号"
                    },
                    "tracking_number": {
                        "type": "string",
                        "description": "快递单号"
                    }
                }
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "search_product",
            "description": "搜索商品，根据关键词查找商品信息、价格、库存等",
            "parameters": {
                "type": "object",
                "properties": {
                    "keyword": {
                        "type": "string",
                        "description": "搜索关键词，例如：手机、笔记本、耳机"
                    },
                    "category": {
                        "type": "string",
                        "description": "商品分类，可选"
                    }
                },
                "required": ["keyword"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "get_available_coupons",
            "description": "查询用户可用的优惠券和促销活动",
            "parameters": {
                "type": "object",
                "properties": {
                    "user_id": {
                        "type": "integer",
                        "description": "用户ID"
                    }
                },
                "required": ["user_id"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "recommend_products",
            "description": "智能推荐商品，根据用户需求、预算、分类等条件推荐最合适的商品，并给出推荐理由",
            "parameters": {
                "type": "object",
                "properties": {
                    "requirements": {
                        "type": "string",
                        "description": "用户需求描述，例如：'主要用来拍照'、'学生用性价比高的'、'办公用轻薄本'"
                    },
                    "min_price": {
                        "type": "number",
                        "description": "最低价格（元），可选"
                    },
                    "max_price": {
                        "type": "number",
                        "description": "最高价格（元），可选"
                    },
                    "category": {
                        "type": "string",
                        "description": "商品分类，例如：数码产品、服饰鞋包、家居生活，可选"
                    },
                    "user_id": {
                        "type": "integer",
                        "description": "用户ID，用于个性化推荐，可选"
                    },
                    "limit": {
                        "type": "integer",
                        "description": "推荐数量，默认5个"
                    }
                },
                "required": ["requirements"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "compare_products",
            "description": "对比多个商品（2-3个），展示价格、评分、参数等对比信息，并指出各项最优者",
            "parameters": {
                "type": "object",
                "properties": {
                    "product_ids": {
                        "type": "array",
                        "items": {"type": "integer"},
                        "description": "要对比的商品ID列表，例如：[1, 2, 3]"
                    }
                },
                "required": ["product_ids"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "get_product_details",
            "description": "获取商品的详细信息，包括规格参数、特点、适用人群、评价等完整信息",
            "parameters": {
                "type": "object",
                "properties": {
                    "product_id": {
                        "type": "integer",
                        "description": "商品ID"
                    },
                    "product_sn": {
                        "type": "string",
                        "description": "商品编号，例如：PHONE-IP15P-001"
                    }
                }
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "search_product_price_history",
            "description": "【推荐】一键查询商品价格历史！当用户询问某个商品的历史价格、价格走势、价格变化、最低价时，优先使用这个工具。只需提供商品名称，会自动搜索商品并返回完整的价格历史数据和统计分析。",
            "parameters": {
                "type": "object",
                "properties": {
                    "product_name": {
                        "type": "string",
                        "description": "商品名称或关键词，例如：'iPhone 15 128GB'、'小米14'"
                    },
                    "days": {
                        "type": "integer",
                        "description": "查询天数，默认5天，支持5/7/30/90"
                    }
                },
                "required": ["product_name"]
            }
        }
    },
    {
        "type": "function",
        "function": {
            "name": "get_price_history",
            "description": "根据商品ID或编号查询价格历史（需要先知道准确的商品编号）",
            "parameters": {
                "type": "object",
                "properties": {
                    "product_id": {
                        "type": "integer",
                        "description": "商品ID"
                    },
                    "product_sn": {
                        "type": "string",
                        "description": "商品编号"
                    },
                    "days": {
                        "type": "integer",
                        "description": "查询天数，默认30天"
                    }
                }
            }
        }
    }
]

